In [71]:
import pandas as pd
import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.colors
In [72]:
df = pd.read_csv('log.csv', parse_dates=['Joined', 'Left', 'Time'])
df['Time'] = df['Left'] - df['Joined']
df = df[df['Member Name'] != 'Sauron']
df
Out[72]:
Member Id Member Name Joined Left Time
0 210743674525450240 stratos1805 2021-04-27 02:21:53.125370 2021-04-27 02:33:41.455374 0 days 00:11:48.330004
1 132415133711466496 סדאם חוסיין ז"ל 2021-04-27 02:21:53.125370 2021-04-27 02:35:55.345365 0 days 00:14:02.219995
2 192642893159202816 MrStormagedon 2021-04-27 02:21:53.125370 2021-04-27 03:10:15.058915 0 days 00:48:21.933545
3 193006567455457280 Cardi Biton 2021-04-27 12:44:58.546623 2021-04-27 12:56:30.695541 0 days 00:11:32.148918
4 192986627998613504 rone 2021-04-27 13:55:12.166887 2021-04-27 14:14:01.761058 0 days 00:18:49.594171
... ... ... ... ... ...
355 193006567455457280 Cardi Biton 2021-05-16 22:57:23.629949 2021-05-16 23:39:09.283086 0 days 00:41:45.653137
356 208115226489389058 XPEZNAZ 2021-05-16 23:20:40.980030 2021-05-17 01:56:10.362019 0 days 02:35:29.381989
357 195596782267269120 Senlers 2021-05-16 23:50:56.933888 2021-05-17 01:56:23.484109 0 days 02:05:26.550221
358 193006567455457280 Cardi Biton 2021-05-17 01:49:50.946648 2021-05-17 02:22:44.480374 0 days 00:32:53.533726
359 439772519378190337 Kosta 2021-05-16 23:50:56.933888 2021-05-17 02:48:00.909960 0 days 02:57:03.976072

358 rows × 5 columns

In [73]:
min_datetime = df['Joined'].min()
max_datetime = df['Left'].max()
min_date, max_date = min_datetime.date(), max_datetime.date()
unique_members = df['Member Name'].unique().tolist()
colormap = plt.cm.tab20b(np.linspace(0, 1, len(unique_members)))
palette = [matplotlib.colors.to_hex(c) for c in colormap]

print(f'Data collected over {max_datetime - min_datetime} with {len(unique_members)} unique members')
Data collected over 20 days 00:26:07.784590 with 19 unique members
In [74]:
# sessions.index = pd.CategoricalIndex(sessions.index, unique_members)
# sessions = sessions.sort_values(ascending=False)
# sessions
# sessions.plot(use_index=True, kind='box', figsize=(20, 5), title='Average Session Length', xlabel='', ylabel='Hours', rot=0, color=colormap)
# plt.show()
plt.figure(figsize=(20, 5))
sns.boxplot(x='Member Name', y=df['Time'].dt.seconds / 3600, data=df, palette=palette)
plt.xlabel('')
plt.ylabel('Hours')
plt.show()
2021-05-17T02:49:07.139145 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [75]:
start = min_datetime.replace(minute=0, second=0, microsecond=0)
end = max_datetime.replace(minute=0, second=0, microsecond=0) + pd.offsets.Hour(1)
attendance_data = []
date = start
while date < end:
    members = df[(df['Joined'] <= date + pd.offsets.Minute(1)) & (df['Left'] >= date)]['Member Name'].to_list()
    attendance_data.append([date] + [member in members for member in unique_members])
    date += pd.offsets.Minute(1)
att_df = pd.DataFrame(attendance_data, columns=['Date'] + unique_members)
In [76]:
axes = att_df.set_index('Date').rolling(1).mean().plot(figsize=(14, 24), yticks=[0, 1], subplots=True, sharex=True, kind='area', color=colormap)
for ax in axes:
    ax.set_yticklabels(['Disconnected', 'Connected'])
plt.subplots_adjust()
plt.show()
2021-05-17T02:49:29.054459 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [77]:
def part_of_day(date):
    if 0 <= date.hour < 6:
        return '00:00 - 05:59'
    elif 6 <= date.hour < 12:
        return '06:00 - 11:59'
    elif 12 <= date.hour < 18:
        return '12:00 - 17:59'
    else:
        return '18:00 - 23:59'

weekday_order = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']

daily_att = att_df.groupby(att_df['Date'].dt.day_name()).sum() / 60
daily_att.index = pd.CategoricalIndex(daily_att.index, weekday_order)
daily_att = daily_att.sort_index()
hourly_att = att_df.groupby(att_df['Date'].apply(part_of_day)).sum() / 60
all_att = att_df.sum() / 60

daily_att.plot(use_index=True, kind='bar', figsize=(20, 5), title='Daily Attendance', xlabel='Weekday', rot=0, color=colormap)
plt.show()
hourly_att.plot(use_index=True, kind='bar', figsize=(20, 5), title='Hourly Attendance', xlabel='Time', rot=0, color=colormap)
plt.show()
all_att.sort_values(ascending=False).plot(use_index=True, kind='bar', figsize=(20, 5), title='Total Attendance', xlabel=None, ylabel='Hours', rot=0, color=colormap)
plt.show()
2021-05-17T02:49:35.096932 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
2021-05-17T02:49:35.977090 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
2021-05-17T02:49:36.532118 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [78]:
corr = att_df.corr()
corr
Out[78]:
stratos1805 סדאם חוסיין ז"ל MrStormagedon Cardi Biton rone יניבצ'וק October Detka XPEZNAZ NightSeeker Groovy Rozi xWarLord_Sharon פליציק OMGitsyuvalT Katya dantzi Senlers Kosta
stratos1805 1.000000 0.592933 0.342003 0.337506 0.131281 0.292580 0.387685 0.101028 0.347009 0.338922 0.175046 0.138649 0.001247 0.047352 0.140812 -0.013673 0.055571 -0.024400 -0.028995
סדאם חוסיין ז"ל 0.592933 1.000000 0.446480 0.382721 0.186916 0.316729 0.388446 0.194582 0.489101 0.316102 0.267469 0.075630 0.035960 0.026260 0.176787 -0.019557 0.038852 -0.034901 -0.041472
MrStormagedon 0.342003 0.446480 1.000000 0.256128 0.105088 0.277449 0.357718 0.309785 0.252681 0.228611 0.194031 0.091293 -0.014640 0.021138 0.042537 0.010982 -0.011705 -0.038154 -0.045337
Cardi Biton 0.337506 0.382721 0.256128 1.000000 0.152628 0.287613 0.352454 0.040106 0.259832 0.185620 0.049448 -0.013744 -0.005874 0.027141 0.154327 -0.009841 -0.008307 -0.015088 0.010781
rone 0.131281 0.186916 0.105088 0.152628 1.000000 0.300955 0.201838 -0.016336 0.253505 0.077589 0.165439 -0.014142 0.382026 -0.011717 0.094050 -0.010056 -0.005505 -0.017946 -0.021325
יניבצ'וק 0.292580 0.316729 0.277449 0.287613 0.300955 1.000000 0.660227 -0.033998 0.194661 0.180584 0.099810 -0.021302 0.095324 0.034326 0.160021 0.074079 0.043196 -0.031391 -0.037301
October 0.387685 0.388446 0.357718 0.352454 0.201838 0.660227 1.000000 -0.017479 0.243361 0.141978 0.176216 -0.022069 0.018678 0.030902 0.021348 0.076709 0.041995 -0.032288 -0.038368
Detka 0.101028 0.194582 0.309785 0.040106 -0.016336 -0.033998 -0.017479 1.000000 0.098388 0.218674 0.043587 0.246396 -0.021572 -0.010983 -0.024728 -0.007712 -0.004222 -0.013763 -0.016354
XPEZNAZ 0.347009 0.489101 0.252681 0.259832 0.253505 0.194661 0.243361 0.098388 1.000000 0.363028 0.105033 -0.020166 -0.034092 0.046006 0.137446 -0.014340 0.052988 0.172725 0.136787
NightSeeker 0.338922 0.316102 0.228611 0.185620 0.077589 0.180584 0.141978 0.218674 0.363028 1.000000 0.090071 0.225265 -0.022949 -0.011684 0.036726 -0.008205 -0.004492 -0.014642 -0.017398
Groovy 0.175046 0.267469 0.194031 0.049448 0.165439 0.099810 0.176216 0.043587 0.105033 0.090071 1.000000 0.102196 -0.016040 0.065882 0.057922 -0.005735 -0.003139 -0.010234 -0.012161
Rozi 0.138649 0.075630 0.091293 -0.013744 -0.014142 -0.021302 -0.022069 0.246396 -0.020166 0.225265 0.102196 1.000000 -0.005459 -0.002779 -0.006258 -0.001952 -0.001069 -0.003483 -0.004139
xWarLord_Sharon 0.001247 0.035960 -0.014640 -0.005874 0.382026 0.095324 0.018678 -0.021572 -0.034092 -0.022949 -0.016040 -0.005459 1.000000 0.000826 -0.012447 -0.003882 -0.002125 -0.006928 -0.008232
פליציק 0.047352 0.026260 0.021138 0.027141 -0.011717 0.034326 0.030902 -0.010983 0.046006 -0.011684 0.065882 -0.002779 0.000826 1.000000 -0.006337 -0.001976 -0.001082 -0.003527 -0.004191
OMGitsyuvalT 0.140812 0.176787 0.042537 0.154327 0.094050 0.160021 0.021348 -0.024728 0.137446 0.036726 0.057922 -0.006258 -0.012447 -0.006337 1.000000 -0.004450 0.127448 -0.007942 -0.009437
Katya -0.013673 -0.019557 0.010982 -0.009841 -0.010056 0.074079 0.076709 -0.007712 -0.014340 -0.008205 -0.005735 -0.001952 -0.003882 -0.001976 -0.004450 1.000000 -0.000760 -0.002477 -0.002943
dantzi 0.055571 0.038852 -0.011705 -0.008307 -0.005505 0.043196 0.041995 -0.004222 0.052988 -0.004492 -0.003139 -0.001069 -0.002125 -0.001082 0.127448 -0.000760 1.000000 -0.001356 -0.001611
Senlers -0.024400 -0.034901 -0.038154 -0.015088 -0.017946 -0.031391 -0.032288 -0.013763 0.172725 -0.014642 -0.010234 -0.003483 -0.006928 -0.003527 -0.007942 -0.002477 -0.001356 1.000000 0.841554
Kosta -0.028995 -0.041472 -0.045337 0.010781 -0.021325 -0.037301 -0.038368 -0.016354 0.136787 -0.017398 -0.012161 -0.004139 -0.008232 -0.004191 -0.009437 -0.002943 -0.001611 0.841554 1.000000
In [79]:
plt.figure(figsize=(20, 20))
sns.heatmap(corr, cmap='YlGnBu')
plt.xticks(rotation=0)
plt.show()
2021-05-17T02:49:36.978432 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [80]:
plt.figure(figsize=(20, 20))
sns.heatmap(corr[(abs(corr) > 0.3)], cmap='YlGnBu')
plt.xticks(rotation=0)
plt.show()
2021-05-17T02:49:37.533947 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [81]:
pd.DataFrame(corr.unstack().sort_values().drop_duplicates())
Out[81]:
0
Kosta MrStormagedon -0.045337
סדאם חוסיין ז"ל -0.041472
October -0.038368
MrStormagedon Senlers -0.038154
יניבצ'וק Kosta -0.037301
... ... ...
סדאם חוסיין ז"ל XPEZNAZ 0.489101
stratos1805 0.592933
October יניבצ'וק 0.660227
Kosta Senlers 0.841554
stratos1805 stratos1805 1.000000

172 rows × 1 columns